***********************************************************************************
********"Macroprudential Regulation, Quantitative Easing, and Bank Lending"********
***********************************************************************************
* This file sets up the dataset with firm level indicators 
***********************************************************************************
***********************************************************************************

**# Bookmark 1 (firm IDs to get firm level indicators from the Company Accounts 
* This part of the code can only be run on a machine that has access to the 
* Bank of Italy data —-- Data Service (CADS), managed by  Cerved.
/*
clear *
global inputdirectory input

import sas using $inputdirectory/bloansn2015total, case(lower)
rename utilizzato disbur
rename accordato com
rename data_oss time

keep if disbur>=30000|com>=30000
drop com
rename ente_segn bankid
rename cod_cen borrowerid
format borrowerid %20.0g
format disbur %20.0g

gen xxx=date(time, "DMY")
drop time
gen time=mofd(xxx)
drop xxx
format time %tm
keep if time>=tm(2014m6)&time<=tm(2015m6)

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) disbur, by(bankid borrowerid time)

merge m:1 bankid using $inputdirectory/INB_preann2014m12, keepus(e13 exlnassets excashas)
keep if _merge==3
drop _merge

keep borrowerid 
duplicates drop borrowerid, force
rename borrowerid elementvalue
odbc exec ("drop table F671750.CODES2015"), dsn("INFOSTAT")
odbc insert elementvalue, table(F671750.CODES2015) dsn("INFOSTAT") create
odbc load, exec("select a.elementvalue as borrowerid, b.codfisc_mef as codfisc from F671750.CODES2015 a, t4969.tacri2attr_sx b where a.elementvalue=b.elementvalue") dsn("INFOSTAT") clear
rename *, lower
*destring(codimp), replace
drop if codfisc==" "
gen xxx=substr(codfisc,6,16)
drop codfisc
rename xxx codfisc
odbc exec ("drop table F671750.CODES2015"), dsn("DB22K")
odbc exec("create table F671750.CODES2015 (borrowerid double, codfisc char(11)) IN DB4938MR.TS493801"), dsn("db22k")
odbc insert borrowerid codfisc, table(F671750.CODES2015) dsn("DB22K")
*POI QUI SCARICO E QUINDI VERIFICO CHE E' ANDATO TUTTO OK
*odbc load, exec("select * from F671750.CODES2015") dsn("DB22K") clear
*/


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/cerveds2015, case(lowe)
sort claric
egen claricbis=group(claric)
tab claric claricbis
drop claric
rename claricbis claric

gen sbig=(claric==3)
keep borrowerid sbig score42

**# Bookmark 3 (saving dataset - firm level indicators)
compress
save $inputdirectory/INB_cerveds2015, replace
